package com.SQLGroup;

import org.springframework.stereotype.Repository;

/**
 * Created by kuibu on 16/1/3.
 */

@Repository
public class SQLAccess {

    /**
     * version1.1 PageDao12Impl.class
     */

    // 对评论进行点赞
    public static final String AddACommentPraise = "INSERT INTO comment_praise(comId,senderId,receiverId,date) VALUES (?,?,?,?)";
    // 新增评论
    public static final String AddACommnet = "INSERT INTO page_comment(pageId,senderId,toComId,date,comContent,hasReply) VALUES (?,?,?,?,?,0)";
    // 对文章评论水文好文
    public static final String AddAPageInfoComment = "INSERT INTO info_comment(userId,pageId,choice) VALUES (?,?,?)";
    // 文章内容反馈,提供信息源
    public static final String AddAPageInfoFeedback = "INSERT INTO comment_inpage(userId,wantedInfo,infoSource,date) VALUES (?,?,?,?)";
    // 取消点赞
    public static final String DeleteACommnetPraise = "DELETE FROM comment_praise WHERE comId=? AND senderId=?";
    // 文章阅读量加1
    public static final String UpdatePageReadNumberPlus = "UPDATE page_info SET readNumber=readNumber+1 WHERE pageId=?";
    // 评论设置有回复标志
    public static final String UpdateHasreplyTrue = "UPDATE page_comment SET hasReply=1 WHERE commentId=?";
    // 新增文章的转发数
    public static final String UpdatePageTransmitNumberPlus = "UPDATE page_info SET transmitNumber=transmitNumber+1 WHERE pageId=?";
    // 新增文章水文评论数
    public static final String UpdatePageCriNumberPlus = "UPDATE page_info set criNum=criNum+1 WHERE pageId=?";
    // 新增文章好文评论数
    public static final String UpdatePagePraiseNumberPlus = "UPDATE page_info set praNum=praNum+1 WHERE pageId=?";
    // 获取文章评论总数
    public static final String SelectPageCommentCount = "SELECT COUNT(*) FROM page_commentlist WHERE pageId=?";
    // 获取文章评论列表
    public static final String SelectPageComment = "SELECT commentId,senderId,userName,userPhoto,comContent,date,praiseNum,replyNum FROM page_commentlist WHERE pageId=? ORDER BY commentId DESC LIMIT ?,?";
    // 获取用户是否点赞了某个评论
    public static final String CheckIsUserPraiseComment = "SELECT id FROM comment_praise WHERE comId=? AND senderId=?";
    // 获取正文信息
    public static final String SelectPageInfo = "SELECT pageImageSrc AS imageSrc,firstTitle,pageContent,readNumber,pageDate,pageFrom,sourceAddress FROM page_info,page_titleimage WHERE page_info.titleImage=page_titleimage.imageId AND pageId=?";
    // 获取推荐文章
    public static final String SelectRecommendPage = "SELECT pageId,firstTitle,secondType,thirdTypeName,imageSrc FROM pagepre_firsttype WHERE pageId >= ((SELECT MAX(pageId) FROM pagepre_firsttype)-(SELECT MIN(pageId) FROM pagepre_firsttype)) * RAND() + (SELECT MIN(pageId) FROM pagepre_firsttype)  AND typeId=? LIMIT 3";
    // 获取文章第一页评论列表
    public static final String SelectFirstPageComment = "SELECT commentId,senderId,userName,userPhoto,comContent,date,praiseNum,replyNum FROM page_commentlist WHERE pageId=? ORDER BY commentId DESC LIMIT 0,?";
    // 获取主界面一个三级分类信息
    public static final String SelectAThirdTypeInfo = "SELECT pageId AS id,imageSrc AS imageString,firstTitle AS textString,readNumber AS num,secondType,thirdTypeName AS classString FROM pagepre_firsttype WHERE typeId=? AND firstType=? ORDER BY pageDate DESC LIMIT 0,?";
    // 获取主界面一个三级分类信息总数
    public static final String SelectAThirdTypeInfoCount = "SELECT COUNT(*) FROM pagepre_third WHERE secondType=? AND thirdTypeName=?";
    // 获取三级分类界面信息
    public static final String SelectAThirdTypePageInfo = "SELECT pageId AS id,imageSrc AS imageString,firstTitle AS textString,readNumber AS num,thirdTypeName AS classString FROM pagepre_third WHERE secondType=? AND thirdTypeName=? ORDER BY pageDate DESC LIMIT ?,?";
    // 获取用户关注信息
    public static final String SelectUserFocusInfo = "SELECT userFocusId FROM user_info WHERE userId=?";
    // 获取三级分类ID
    public static final String SelectThirdTypeId = "SELECT id FROM type_info WHERE secondType=? AND thirdTypeName=?";
    // 获取评论回复列表
    public static final String SelectCommentReply = "SELECT userName AS replyerName,userPhoto AS replyerPhoto,date AS replyDate,comContent AS replyContent FROM user_info,page_comment WHERE page_comment.senderId=user_info.userId AND toComId=? ORDER BY commentId DESC";
    // 获取未登陆用户一个三级分类信息
    public static final String SelectAThirdTypeInfoNoLogin = "SELECT pageId AS id,firstTitle AS textString,imageSrc AS imageString,readNumber AS num,secondType,thirdTypeName AS classString FROM pagepre_second WHERE typeId=? ORDER BY pageDate DESC LIMIT 0,?";
    // 获取一级分类滑图信息
    public static final String SelectFirstTypeSlideInfo = "SELECT pageId AS id,firstTitle AS textString,imageSrc2 AS imageString,secondType,thirdTypeName AS classString,readNumber FROM page_title WHERE firstType=? ORDER BY pageId DESC LIMIT 0,?";
    //public static final String test = "";
    // 批量删除用户收藏
    public static final String DeleteUserCollectPage = "DELETE FROM user_collect WHERE userId=? AND collectId IN (?)";


    /**
     * version1.1 UserDao12Impl.class
     */
    // 根据用户ID获取用户个人信息
    public static final String SelectUserInfoWithId = "SELECT userId,openid,thirdtoken,tokenRY,userName,userPasswd,userPhoto,userSex,userMail,userTel,userCollectNumber,userFocusId FROM user_info WHERE userId=?";
    // 新增用户
    public static final String AddAUser = "INSERT INTO user_info(userMail,userTel,userPasswd,registerDate,registerFrom,province,city) VALUES (?,?,?,?,0,?,?)";
    // 根据邮箱号获取用户个人信息
    public static final String SelectUserInfoWithMail = "SELECT userId,userName,userCoin,userPhoto,userSex,userCollectNumber,userFocusId,school,schoolYear,height,weight,registerDate FROM user_info WHERE userMail=? AND userPasswd=?";
    // 根据手机号获取用户个人信息
    public static final String SelectUserInfoWithTel = "SELECT userId,userName,userCoin,userPhoto,userSex,userCollectNumber,userFocusId,school,schoolYear,height,weight,registerDate FROM user_info WHERE userTel=? AND userPasswd=?";
    // 根据用户ID获取用户个人信息
    public static final String SelectUserInfoWithIdAndPasswd = "SELECT userId,userName,userCoin,userPhoto,userSex,userCollectNumber,userFocusId,school,schoolYear,height,weight,registerDate FROM user_info WHERE userId=? AND userPasswd=?";
    // 更新用户昵称,头像,性别
    public static final String UpdateUserNamePhotoSex = "UPDATE user_info SET userName=?,userPhoto=?,userSex=? WHERE userId=?";
    // 更新用户昵称,头像
    public static final String UpdateUserNamePhoto = "UPDATE user_info SET userName=?,userPhoto=? WHERE userId=?";
    // 更新用户昵称,性别
    public static final String UpdateUserNameSex = "UPDATE user_info SET userName=?,userSex=? WHERE userId=?";
    // 更新用户昵称
    public static final String UpdateUserName = "UPDATE user_info SET userName=? WHERE userId=?";
    // 更新用户头像,性别
    public static final String UpdateUserPhotoSex = "UPDATE user_info SET userPhoto=?,userSex=? WHERE userId=?";
    // 更新用户头像
    public static final String UpdateUserPhoto = "UPDATE user_info SET userPhoto=? WHERE userId=?";
    // 更新用户性别
    public static final String UpdateUserSex = "UPDATE user_info SET userSex=? WHERE userId=?";
    // 更新用户学校和入学年份
    public static final String UpdateUserSchoolYear = "UPDATE user_info SET school=?,schoolYear=? WHERE userId=?";
    // 更新用户学校
    public static final String UpdateUserSchool = "UPDATE user_info SET school=? WHERE userId=?";
    // 更新用户入学年份
    public static final String UpdateUserYear = "UPDATE user_info SET schoolYear=? WHERE userId=?";
    // 更新用户关注信息
    public static final String UpdateUserFocusInfo = "UPDATE user_info SET userFocusId=? WHERE userId=?";
    // 获取某个人评论的回复总数
    public static final String SelectCommentReplyCount = "SELECT COUNT(*) FROM page_commentreply WHERE receiverId=?";
    // 获取用户回复消息
    public static final String SelectUserReplyMessage = "SELECT commentId,userName,userPhoto,replyContent,comment,date,pageId FROM page_commentreply WHERE receiverId=? ORDER BY commentId DESC LIMIT ?,?";
    // 更新用户密码
    public static final String UpdateUserPasswd = "UPDATE user_info SET userPasswd=? WHERE userId=?";
    // 取消对某个用户的评论点赞
    public static final String UpdateAbordCommentPraise = "UPDATE comment_praise SET hasPraise=0 WHERE receiverId=?";
    // 获取用户评论被点赞的总数
    public static final String SelectUserCommentPraiseNumber = "SELECT COUNT(*) FROM page_commentpraise WHERE receiverId=?";
    // 获取用户点赞消息
    public static final String SelectUserPraiseMessage = "SELECT userName,userPhoto,comContent,date FROM page_commentpraise WHERE receiverId=? ORDER BY commentId DESC LIMIT ?,?";
    // 新增反馈信息
    public static final String AddAFeedbackInfo = "INSERT INTO user_feedback(userId,date,type,content,screenShot1,screenShot2,screenShot3,screenShot4) VALUES (?,?,?,?,?,?,?,?)";
    // 新增收藏
    public static final String AddAColleckInfo = "INSERT INTO user_collect(userId,pageId,date) VALUES (?,?,?)";
    // 删除一条收藏信息
    public static final String DeleteAColleckInfo = "DELETE FROM user_collect WHERE userId=? AND pageId=?";
    // 获取用户收藏文章总数
    public static final String SelectUserColleckCount = "SELECT COUNT(*) FROM page_usercollect WHERE userId=?";
    // 获取用户收藏文章列表
    public static final String SelectUserColleckList = "SELECT collectId,pageId,firstTitle,secondType,thirdTypeName,date,imageSrc,collectNumber FROM page_usercollect WHERE userId=? ORDER BY collectId DESC LIMIT ?,?";
    // 根据二级分类ID获取用户对应二级分类收藏总数
    public static final String SelectUserColleckCountOfSecondType = "SELECT COUNT(*) FROM page_usercollect WHERE userId=? AND secondType=?";
    // 根据二级分类ID获取用户对应收藏信息列表
    public static final String SelectUserCollerckListOfSecondType = "SELECT collectId,pageId,firstTitle,secondType,thirdTypeName,date,imageSrc,collectNumber FROM page_usercollect WHERE userId=? AND secondType=? ORDER BY collectId DESC LIMIT ?,?";
    // 获取用户所有收藏信息
    public static final String SelectAllUserColleckInfo = "SELECT collectId,pageId,firstTitle,thirdTypeName,date,imageSrc,collectNumber FROM page_usercollect WHERE userId=?";
    // 根据手机号获取用户收藏文章id集合
    public static final String SelectUserColleckPageIdsWithTelLogin = "SELECT pageId FROM user_info,user_collect WHERE user_info.userId=user_collect.userId AND user_info.userTel=?";
    // 根据邮箱号获取用户收藏文章id集合
    public static final String SelectUserColleckPageIdsWithMailLogin = "SELECT pageId FROM user_info,user_collect WHERE user_info.userId=user_collect.userId AND user_info.userMail=?";
    // 根据邮箱号和手机号获取用户收藏文章id集合
    public static final String SelectUserColleckPageIdsWithMailAndTelLogin = "SELECT pageId FROM user_info,user_collect WHERE user_info.userId=user_collect.userId AND user_info.userMail=? AND userTel=?";
    // 根据用户ID获取用户收藏文章id集合
    public static final String SelectUserColleckPageIdsWithUserId = "SELECT pageId FROM user_collect WHERE userId=?";
    // 根据邮箱号验证用户是否存在
    public static final String SelectIsUserExistWithMail = "SELECT userId FROM user_info WHERE userMail=?";
    // 根据手机号验证用户是否存在
    public static final String SelectIsUserExistWithTel = "SELECT userId FROM user_info WHERE userTel=? LIMIT 1";
    // 获取用户是否有点赞消息
    public static final String SelectUserIsHasCommentPraiseMessage = "SELECT id FROM comment_praise WHERE receiverId=? AND hasPraise=1";
    // 获取用户是否有回复消息
    public static final String SelectUserIsHasCommentReplyMessage = "SELECT commentId FROM page_comment WHERE senderId=? AND hasReply=1";
    // 获取用户是否有系统消息
    public static final String SelectUserIsHasSystemMessage = "SELECT messageId FROM system_message WHERE receiverId=? AND hasSysMessage=1";
    // 删除用户有回复消息标志
    public static final String UpdateUserReplyTagRemove = "UPDATE page_comment SET hasReply=0 WHERE senderId=?";
    // 获取系统消息
    public static final String SelectSystemMessage = "SELECT message,date FROM system_message WHERE receiverId=? ORDER BY messageId DESC";
    // 根据手机号更新用户密码
    public static final String UpdateUserPasswdWithTel = "UPDATE user_info SET userPasswd=? WHERE userTel=?";
    // 根据用户ID更新融云用户token
    public static final String UpdateRongYunToken = "UPDATE user_info SET tokenRY=? WHERE userId=?";
    // public static final String test = "";

    /**
     * version1.1 SubscriDao12Impl.class
     */
    // 获取三级分类收藏数排序信息
    public static final String SelectTypeInfoByCollectNumber = "SELECT secondType,thirdTypeName,typeImgSrc FROM type_info ORDER BY collectedNumber DESC LIMIT ?,?";
    // 获取推荐文章总数
    public static final String SelectRecommendCount = "SELECT COUNT(*) FROM page_recommend";
    // 获取推荐界面信息
    public static final String SelectRecommendInfo = "SELECT pageId,imageSrc2,firstTitle,readNumber,secondType,thirdTypeName FROM page_recommend WHERE recomPos>1 ORDER BY secondType ASC,typeId ASC,pageDate DESC LIMIT ?,?";
    // 获取精选文章信息
    public static final String SelectChosenRecommendInfo = "SELECT pageId,imageSrc2 AS imageSrc,firstTitle,secondType,thirdTypeName,recomPos FROM page_recommend WHERE recomPos=? ORDER BY pageDate DESC LIMIT 1";
    // 获取整合文章信息
    public static final String SelectConformRecommendInfo = "SELECT pageId,imageSrc2 AS imageSrc,secondTitle AS firstTitle,secondType,thirdTypeName,recomPos FROM page_newrecom WHERE recomPos=? ORDER BY pageDate DESC LIMIT 1";
    // 获取Break文章信息
    public static final String SelectBreakRecommendInfo = "SELECT pageId,imageSrc2 AS imageSrc,secondTitle AS firstTitle,secondType,thirdTypeName,recomPos FROM page_newrecom WHERE recomPos=? ORDER BY pageDate DESC LIMIT 1";
    // 获取专题1文章信息
    public static final String SelectSubject1RecommendInfo = "SELECT pageId,imageSrc2 AS imageSrc,secondTitle AS firstTitle,readNumber,secondType,thirdTypeName FROM page_newrecom WHERE recomPos=10 ORDER BY subjectOrderIndex ASC";
    // 获取专题2文章信息
    public static final String SelectSubject2RecommendInfo = "SELECT pageId,imageSrc2 AS imageSrc,secondTitle AS firstTitle,readNumber,secondType,thirdTypeName FROM page_newrecom WHERE recomPos=12 ORDER BY subjectOrderIndex ASC";
    // 获取专题3文章信息
    public static final String SelectSubject3RecommendInfo = "SELECT pageId,imageSrc2 AS imageSrc,secondTitle AS firstTitle,readNumber,secondType,thirdTypeName FROM page_newrecom WHERE recomPos=14 ORDER BY subjectOrderIndex ASC";
    // 获取专题4文章信息
    public static final String SelectSubject4RecommendInfo = "SELECT pageId,imageSrc2 AS imageSrc,secondTitle AS firstTitle,readNumber,secondType,thirdTypeName FROM page_newrecom WHERE recomPos=16 ORDER BY subjectOrderIndex ASC";
    // 获取专题5文章信息
    public static final String SelectSubject5RecommendInfo = "SELECT pageId,imageSrc2 AS imageSrc,secondTitle AS firstTitle,readNumber,secondType,thirdTypeName FROM page_newrecom WHERE recomPos=18 ORDER BY subjectOrderIndex ASC";
    // 获取专题6文章信息
    public static final String SelectSubject6RecommendInfo = "SELECT pageId,imageSrc2 AS imageSrc,secondTitle AS firstTitle,readNumber,secondType,thirdTypeName FROM page_newrecom WHERE recomPos=20 ORDER BY subjectOrderIndex ASC";
    // 获取推荐界面滑图信息
    public static final String SelectRecommendSlidInfo = "SELECT pageId,firstTitle,imageSrc2 AS imageSrc,secondType,thirdTypeName,readNumber FROM page_recommend WHERE recomPos=1 ORDER BY pageId DESC LIMIT 0,?";
    // 根据三级分类名称获取对应一级分类ID
    public static final String SelectFirstTypeOfThirdType = "SELECT firstType FROM type_info WHERE secondType=? AND thirdTypeName=?";
    // 更新三级分类收藏数减1
    public static final String UpdateThirdTypeCollectNumberMinus = "UPDATE type_info SET collectedNumber=collectedNumber-1 WHERE secondType=? AND thirdTypeName=?";
    // public static final String test = "";


    /**
     * version1.2 更新SQL语句
     */
    // 获取主界面一个三级分类信息
    public static final String SelectAThirdTypeInfoVersion1_2 = "SELECT pageId,imageSrc,infoImgType,firstTitle AS pageTitle,readNumber,secondType,thirdTypeName FROM pagepre_firsttype WHERE typeId=? ORDER BY pageDate DESC LIMIT 0,?";
    // 获取一级分类滑图信息
    public static final String SelectFirstTypeSlideInfoVersion1_2 = "SELECT pageId,firstTitle AS pageTitle,imageSrc2 AS imageSrc,secondType,thirdTypeName,readNumber FROM page_title WHERE firstType=? ORDER BY pageId DESC LIMIT 0,?";
    // 获取三级分类界面信息
    public static final String SelectAThirdTypePageInfoVersion1_2 = "SELECT pageId,imageSrc,firstTitle AS pageTitle,readNumber,secondType,thirdTypeName FROM pagepre_third WHERE secondType=? AND thirdTypeName=? ORDER BY pageDate DESC LIMIT ?,?";
    // 获取推荐文章
    public static final String SelectRecommendPageVersion1_2 = "SELECT pageId,pageDate,firstTitle AS pageTitle,secondType,thirdTypeName,imageSrc FROM pagepre_firsttype WHERE pageId >= ((SELECT MAX(pageId) FROM pagepre_firsttype)-(SELECT MIN(pageId) FROM pagepre_firsttype)) * RAND() + (SELECT MIN(pageId) FROM pagepre_firsttype)  AND typeId=? LIMIT 3";
    // 获取推荐界面滑图信息
    public static final String SelectRecommendSlidInfoVersion1_2 = "SELECT pageId,firstTitle AS pageTitle,imageSrc2 AS imageSrc,secondType,thirdTypeName,readNumber FROM page_recommend WHERE recomPos=1 ORDER BY pageId DESC LIMIT 0,?";
    // 获取精选文章信息
    public static final String SelectChosenRecommendInfoVersion1_2 = "SELECT pageId,imageSrc2 AS imageSrc,secondTitle AS pageTitle,secondType,thirdTypeName,recomPos FROM page_newrecom WHERE recomPos=? ORDER BY pageDate DESC LIMIT 1";
    // 获取整合文章信息
    public static final String SelectConformRecommendInfoVersion1_2 = "SELECT pageId,imageSrc2 AS imageSrc,secondTitle AS pageTitle,secondType,thirdTypeName,recomPos FROM page_newrecom WHERE recomPos=? ORDER BY pageDate DESC LIMIT 1";
    // 获取Break文章信息
    public static final String SelectBreakRecommendInfoVersion1_2 = "SELECT pageId,imageSrc2 AS imageSrc,secondTitle AS pageTitle,secondType,thirdTypeName,recomPos FROM page_newrecom WHERE recomPos=? ORDER BY pageDate DESC LIMIT 1";
    // 获取专题1文章信息
    public static final String SelectSubject1RecommendInfoVersion1_2 = "SELECT pageId,imageSrc2 AS imageSrc,secondTitle AS pageTitle,readNumber,secondType,thirdTypeName FROM page_newrecom WHERE recomPos=10 ORDER BY subjectOrderIndex ASC";
    // 获取专题2文章信息
    public static final String SelectSubject2RecommendInfoVersion1_2 = "SELECT pageId,imageSrc2 AS imageSrc,secondTitle AS pageTitle,readNumber,secondType,thirdTypeName FROM page_newrecom WHERE recomPos=12 ORDER BY subjectOrderIndex ASC";
    // 获取专题3文章信息
    public static final String SelectSubject3RecommendInfoVersion1_2 = "SELECT pageId,imageSrc2 AS imageSrc,secondTitle AS pageTitle,readNumber,secondType,thirdTypeName FROM page_newrecom WHERE recomPos=14 ORDER BY subjectOrderIndex ASC";
    // 获取专题4文章信息
    public static final String SelectSubject4RecommendInfoVersion1_2 = "SELECT pageId,imageSrc2 AS imageSrc,secondTitle AS pageTitle,readNumber,secondType,thirdTypeName FROM page_newrecom WHERE recomPos=16 ORDER BY subjectOrderIndex ASC";
    // 获取专题5文章信息
    public static final String SelectSubject5RecommendInfoVersion1_2 = "SELECT pageId,imageSrc2 AS imageSrc,secondTitle AS pageTitle,readNumber,secondType,thirdTypeName FROM page_newrecom WHERE recomPos=18 ORDER BY subjectOrderIndex ASC";
    // 获取专题6文章信息
    public static final String SelectSubject6RecommendInfoVersion1_2 = "SELECT pageId,imageSrc2 AS imageSrc,secondTitle AS pageTitle,readNumber,secondType,thirdTypeName FROM page_newrecom WHERE recomPos=20 ORDER BY subjectOrderIndex ASC";
    // 获取用户收藏文章列表
    public static final String SelectUserColleckListVersion1_2 = "SELECT collectId,pageId,firstTitle AS pageTitle,secondType,thirdTypeName,date,imageSrc,collectNumber FROM page_usercollect WHERE userId=? ORDER BY collectId DESC LIMIT ?,?";
    // 根据二级分类ID获取用户对应收藏信息列表
    public static final String SelectUserCollerckListOfSecondTypeVersion1_2 = "SELECT collectId,pageId,firstTitle AS pageTitle,secondType,thirdTypeName,date,imageSrc,collectNumber FROM page_usercollect WHERE userId=? AND secondType=? ORDER BY collectId DESC LIMIT ?,?";


    /**
     * version1.3 更新SQL语句
     */
    // 获取主界面一个三级分类信息总数
    public static final String SelectAThirdTypeInfoCountVersion1_3 = "SELECT COUNT(*) FROM pagepre_third WHERE thirdTypeId=?";
    // 获取三级分类界面信息
    public static final String SelectAThirdTypePageInfoVersion1_3 = "SELECT pageId,imageSrc,firstTitle AS pageTitle,readNumber,secondType,thirdTypeName FROM pagepre_third WHERE thirdTypeId=? ORDER BY pageDate DESC LIMIT ?,?";
    // 获取新格式的用户关注分类
    public static final String SelectUserFocusInfoVersion1_3 = "SELECT userFocusTypeIds FROM user_info WHERE userId=?";
    // 更新新格式的用户关注信息
    public static final String UpdateUserFocusInfoVersion1_3 = "UPDATE user_info SET userFocusTypeIds=? WHERE userId=?";
    // 获取三级分类描述
    public static final String SelectThirdTypeDescriptionVersion1_3 = "SELECT description FROM type_info WHERE thirdTypeId=?";
    // 根据手机号获取用户个人信息
    public static final String SelectUserInfoWithTelVersion1_3 = "SELECT userId,tokenRY,userName,userCoin,userPhoto,userSex,userCollectNumber,userFocusTypeIds,school,schoolYear,height,weight FROM user_info WHERE userTel=? AND userPasswd=?";
    // 根据用户ID获取用户个人信息
    public static final String SelectUserInfoWithIdAndPasswdVersion1_3 = "SELECT userId,tokenRY,userName,userCoin,userPhoto,userSex,userCollectNumber,userFocusTypeIds,school,schoolYear,height,weight FROM user_info WHERE userId=? AND userPasswd=?";
    // 获取三级分类信息
    public static final String SelectThridTypePageInfoVersion1_3 = "SELECT pageId,pageDate,firstTitle AS pageTitle,imageSrc,readNumber,secondType,thirdTypeName,infoImgType FROM pagepre_second WHERE typeId=? ORDER BY pageDate DESC,infoImgType DESC LIMIT 0,?";
    // 获取用户置顶三级分类信息
    public static final String SelectUserTopTypeIdsVersion1_3 = "SELECT userTopTypeIds FROM user_info WHERE userId=?";
    // 更新用户置顶三级分类信息
    public static final String UpdateUserTopTypeIdsVersion1_3 = "UPDATE user_info SET userTopTypeIds=? WHERE userId=?";
    // 记录文章在分享后被阅读
    public static final String UpdatePageWebReadedNumberVersion1_3 = "UPDATE page_info SET webReadNumber=webReadNumber+1,readNumber=readNumber+1 WHERE pageId=?";
    // 查询用户邀请码
    public static final String SelectUserInviteCodeVersion1_3 = "SELECT inviteCode FROM user_info WHERE userId=?";
    // 查询用户邀请人数
    public static final String SelectUserInvitedFriendsNumberVersion1_3 = "SELECT COUNT(*) FROM user_info where inputInviteCode=?";
    // 用户输入邀请码
    public static final String UpdateUserInviteCodeVersion1_3 = "UPDATE user_info SET inputInviteCode=? WHERE userId=?";
    // 查询用户输入的邀请码
    public static final String SelectUserInputInviteCodeVersion1_3 = "SELECT userTel,inputInviteCode FROM user_info WHERE userId=?";
    // 新增用户
    public static final String AddAHtmlUser = "INSERT INTO user_info(inputInviteCode,userTel,userPasswd,registerFrom,registerDate,province,city) VALUES (?,?,?,1,?,?,?)";
    // 根据手机号获取用户邀请码
    public static final String SelectUserInviteCodeWithTelVersion1_3 = "SELECT inviteCode FROM user_info WHERE userTel=?";
    // 更新用户自己邀请码
    public static final String UpdateUserselfInviteCodeVersion1_3 = "UPDATE user_info SET inviteCode=? WHERE userId=?";
    // 新增用户
    public static final String AddAUserVersion1_3 = "INSERT INTO user_info(inputInviteCode,uuid,userTel,userPasswd,registerDate,registerFrom,province,city) VALUES (?,?,?,?,?,0,?,?)";


    /*活动版块SQL语句集合*/

    // 更新活动用户参与人数
    public static final String UpdateActivityJoinNumberVersion1_3 = "UPDATE activity SET joinNumber=joinNumber+1 WHERE activityID=?";
    // 根据活动类型获取活动信息
    public static final String SelectActivityInfoByTypeVersion1_3 = "SELECT activityID,activityTitleImage,activityTitle,joinNumber,link FROM activity WHERE activityType=? AND activityID NOT IN(13,14,15)";
    // 根据活动ID获取活动信息
    public static final String SelectActivityInfoByID_Version1_3 = "SELECT activityBanner,activityTitle,joinNumber,address,date,activityDescription FROM activity WHERE activityId=?";
    // 获取用户我的活动界面信息
    public static final String SelectUserActivityInfo = "SELECT activityID,activityTitle,days,date,coin,money FROM user_activityinfo WHERE userId=?";
    // 获取用户特定活动信息
    public static final String SelectUserSpecificActivityInfo = "SELECT days,coin,money FROM user_activityinfo WHERE userId=? AND activityID=?";
    // 获取用户一周打卡情况信息
    public static final String SelectUserWeekLaunchCardInfo = "SELECT date FROM activity_launchrecord WHERE userId=? AND activityID=? ORDER BY date DESC LIMIT 0,7";
    // 获取用户打卡界面信息
    public static final String SelectUserLaunchPageInfo = "SELECT * FROM (SELECT tt.userId,tt.activityID,COUNT(tt.id) AS days,SUM(tt.coin) AS coin,SUM(tt.money) AS money,SUM(tt.calorie) AS calorie,tt.currData,MAX(tt.currData) AS maxCurrData,SUM(tt.currData) AS currDataCount,tt.data1,tt.data3,tt.date FROM (SELECT id,userId,activityID,date,currData,data1,data2,data3,coin,calorie,money FROM activity_launchrecord WHERE userId=? AND activityID=? ORDER BY date DESC) AS tt )aa";
    // 新增用户打卡信息
    public static final String AddUserLaunchCardInfo = "INSERT INTO activity_launchrecord(userId,activityID,date,currData,data1,data3,coin,money) VALUES (?,?,?,?,?,?,?,?)";
    // 获取用户今日打卡数据
    public static final String SelectUserTodayCurrentData = "SELECT currData FROM activity_launchrecord WHERE userId=? AND activityID=? AND date=CURRENT_DATE";
    // 更新用户今日打卡数据 - 刷新数据
    public static final String UpdateUserTodayLaunchCardData = "UPDATE activity_launchrecord SET currData=?,coin=?,money=?,data3=data3+? WHERE  userId=? AND activityID=? AND date=CURRENT_DATE";
    // 更新用户今日打卡数据 - 累加数据
    public static final String  AddNewDataToUserCurrentLaunchCardInfo= "UPDATE activity_launchrecord SET currData=?,data1=?,coin=?,money=? WHERE userId=? AND activityID=? AND date=CURRENT_DATE";
    // 更新用户今日打卡积分 - 增加
    public static final String AddUserTodayLaunchCardCoin = "UPDATE activity_launchrecord SET coin=coin+? WHERE userId=? AND activityID=? AND date=CURRENT_DATE";
    // 获取公益跑活动捐赠金额
    public static final String SelectUserActivityDonateMoneyGongyipao = "SELECT * FROM (SELECT tt.userId,tt.activityID,SUM(tt.money) AS money,tt.currData,SUM(tt.currData) AS currDataCount FROM (SELECT id,userId,activityID,date,currData,data1,data2,coin,calorie,money FROM activity_launchrecord ORDER BY date DESC) AS tt GROUP BY tt.userId,tt.activityID) aa WHERE aa.userId=? AND aa.activityID=?";
    // 获取平板支撑活动捐赠金额
    public static final String SelectUserActivityDonateMoneyPingban = "SELECT * FROM (SELECT tt.userId,tt.activityID,SUM(tt.money) AS money,tt.currData,SUM(tt.currData) AS currDataCount FROM (SELECT id,userId,activityID,date,currData,data1,data2,coin,calorie,money FROM activity_launchrecord ORDER BY date DESC) AS tt GROUP BY tt.userId,tt.activityID) aa WHERE aa.userId=? AND aa.activityID=?";
    // 获取俯卧撑和仰卧起坐活动捐赠金额
    public static final String SelectUserActivityDonateMoneyFuwochengAndYangwo = "SELECT * FROM (SELECT tt.userId,tt.activityID,SUM(tt.money) AS money,tt.currData,tt.data1,SUM(tt.data1) AS data1Count FROM (SELECT id,userId,activityID,date,currData,data1,money FROM activity_launchrecord ORDER BY date DESC) AS tt GROUP BY tt.userId,tt.activityID) aa WHERE aa.userId=? AND aa.activityID=?";
    // 获取用户今日某活动最新打卡数据
    public static final String SelectUserTodayActivityInfo = "SELECT id,currData,data1,coin,calorie FROM activity_launchrecord WHERE userId=? AND activityID=? AND date=CURRENT_DATE";
    // 获取用户打卡活动个数
    public static final String SelectUserLaunchCardActivityNumber = "SELECT COUNT(id) FROM activity_launchrecord WHERE userId=? AND date=CURRENT_DATE";
    // 获取用户打卡活动总个数
    public static final String SelectUserLaunchCardActivityNumberCount = "SELECT COUNT(id) FROM activity_launchrecord WHERE userId=?";
    // 记录用户分享活动参与信息
    public static final String RecordUserShareActivity = "UPDATE activity_launchrecord SET data2=1 WHERE userId=? AND activityID=? AND date=CURRENT_DATE";
    // 获取用户活动分享积分
    public static final String SelectUserActivityShareCoin  = "SELECT COUNT(id) FROM activity_launchrecord WHERE userId=? AND data2=1 AND date=CURRENT_DATE";
    // 获取用户活动分享总积分
    public static final String SelectUserActivityShareCoinCount  = "SELECT COUNT(id) FROM activity_launchrecord WHERE userId=? AND data2=1";
    // 根据第三方openid获取用户信息 - iOS版本
    public static final String SelectUserInfoWithThirdPartOpenidIOSVersion = "SELECT userId,thirdtoken AS token,userName,userCoin,userPhoto,userSex,userCollectNumber,userFocusTypeIds,school,schoolYear,height,weight FROM user_info WHERE openid=?";
    // 根据第三方openid获取用户信息 - Android版本
    public static final String SelectUserInfoWithThirdPartOpenidAndroidVersion = "SELECT userId,thirdtoken AS token,userName,userCoin,userPhoto,userSex,userCollectNumber,userFocusId,school,schoolYear,height,weight FROM user_info WHERE openid=?";
    // 获取用户参与活动信息
    public static final String SelectUserJoinActivityInfo = "SELECT joinActivityIds FROM user_info where userId=?";
    // 更新用户活动参与信息
    public static final String UpdateUserJoinActivityInfo = "UPDATE user_info SET joinActivityIds=? WHERE userId=?";
    // 设置用户身体信息
    public static final String UpdateUserBodyInfo = "UPDATE user_info SET height=?,weight=?,name=?,school=? WHERE userId=?";
    // 获取用户身高体重等信息
    public static final String SelectUserBodyInfo = "SELECT height,weight FROM user_info WHERE userId=?";
    // 使用第三方平台注册跬步
    public static final String AddNewUserWithThridPart = "INSERT INTO user_info(openid,thirdtoken,uuid,registerFrom,registerDate) VALUES (?,?,?,0,?)";
    // 获取用户个人信息
    public static final String SelectUserInfoOfMap = "SELECT userId,openid,thirdtoken AS token,userName,userPhoto,userSex,school,schoolYear,userCollectNumber,userFocusTypeIds AS userFocusId FROM user_info WHERE userId=?";
    // 更新用户第三方token
    public static final String UpdateUserThirdPartTokenInfo = "UPDATE user_info SET thirdtoken=? WHERE userId=?";
    // 获取用户资讯版块各模块积分
    public static final String SelectModuleUserCoin = "SELECT coin FROM coin_modulerecord WHERE userId=? AND `module`=? AND `action`=? AND `date`=CURRENT_DATE";
    // 获取用户资讯版块个模块总积分
    public static final String SelectModuleUserCoinCount = "SELECT SUM(coin) AS coin FROM coin_modulerecord WHERE userId=? AND `module`=? AND `action`=?";
    // 增加用户总积分
    public static final String UpdateUserCountCoin = "UPDATE user_info SET userCoin=userCoin+? WHERE userId=?";
    // 获取用户总积分
    public static final String SelectUserCountCoin = "SELECT userCoin,userShareAppCoin,userCompleteInfoCoin FROM user_info WHERE userId=?";
}
